package com.ericsson.middleware.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.util.logging.Logger;

@Component
public class MySQLDBCreator {
		
	private static DataSource dataSource;
	
	private final static Logger LOGGER = Logger.getLogger(MySQLDBCreator.class
			.getName());
	
	private String[] createTable = { 
			"CREATE TABLE lamp_state ( " +
			"    ID text NOT NULL, " +
			"    SERIALID text NOT NULL, " +
			"    CURRENTINTENSITY integer UNSIGNED NOT NULL, " +
			"    PWM integer UNSIGNED NOT NULL, " +
			"    TIMESTAMP text NOT NULL, " +
			"    LATITUDE text NOT NULL, " +
			"    LONGITUDE text NOT NULL, " +		
			"    PRIMARY KEY (ID) " +
			") ",
			
			"CREATE TABLE parking_spot_state ( " +
					"    ID text NOT NULL, " +
					"    STATUS text NOT NULL, " +
					"    LATITUDE text NOT NULL, " +
					"    LONGITUDE text NOT NULL, " +		
					"    PRIMARY KEY (ID) " +
					") "						
					};
	
	private String testDB = "SELECT * FROM lamp_state";
	private boolean created = false;
	
	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public boolean dbExists() {
		Connection conn = null;
		boolean exists = false;
		try  {
			conn = dataSource.getConnection();
			PreparedStatement stmt = conn.prepareStatement(testDB);
			LOGGER.log(Level.INFO, "Executing DB test query...." + testDB);
			stmt.executeQuery();
			conn.close();
			exists = true;
		} catch (SQLException e) {
			LOGGER.log(Level.SEVERE, "DB doesnt exists: " + e.getMessage(), e);
		}
		
		return exists;
	}
	
	
	public boolean createDB() {
		Connection conn = null;
		created = false;
		
		LOGGER.log(Level.INFO, "Will create DB.: " + createTable);
		try {
			conn = dataSource.getConnection();
			for (String str : createTable) {
				PreparedStatement ps = conn.prepareStatement(str);
				ps.executeUpdate();
				ps.close();
			}
			conn.close();
			created = true;
		} catch (SQLException e) {
			LOGGER.log(Level.SEVERE, "Failed to create DB." + e.getMessage());
		}
		return created;
	}
	
	public void checkAndCreate() {
		LOGGER.log(Level.INFO, "*****DB1 ");
		if (created == false) {
			LOGGER.log(Level.INFO, "*****DB2 ");
			synchronized (this) { 
				if (created == false) {
					LOGGER.log(Level.INFO, "*****DB3 ");
					try (Connection connect = dataSource.getConnection()) {
						LOGGER.log(Level.INFO, "Checking if DB exists.");
						if (!dbExists()) {
							LOGGER.log(Level.INFO, "Database doesn't exists. Creating.");
							created = createDB();
							LOGGER.log(Level.INFO, "Database created.");
						} else {
							created = true;
							LOGGER.log(Level.INFO, "Database exists. ");
							connect.close();
						}
					} catch (SQLException e) {
						LOGGER.log(Level.INFO, "Failed to get connection.", e);
					}
				}
			}
		}
		LOGGER.log(Level.INFO, "*****DB4 ");
	}
}
